//
//  dataBaseHandle.m
//  HealthA段
//
//  Created by dllo on 15/6/10.
//  Copyright (c) 2015年 www.lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "dataBaseHandle.h"

@implementation dataBaseHandle

//单例方法
+(instancetype)shareDB
{
    static dataBaseHandle *dataBase = nil;
    static dispatch_once_t ontToTake;
    dispatch_once(&ontToTake, ^{
        dataBase = [[dataBaseHandle alloc] init];
    });
    return dataBase;
}


//打开数据库
- (void)openDB
{
    //获取沙盒路径
    NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sandBoxPath = [sandBox lastObject];
    //拼接数据库路径
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"Health.sqlite"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"开启数据库成功");
        NSLog(@"%@",dbPath);
    }else{
        NSLog(@"开启数据库失败");
    }
}

//健康知识表

//创建表
- (void)creatKnowTable
{
    //sql语句
    NSString *sqlStr = @"create table if not exists Know(title text , imgUrl text, id int,number integer primary key autoincrement)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
//插入收藏
-(void)insertKnowthWithTitle:(NSString *)title imgUrl:(NSString *)imgUrl id:(NSInteger)id
{
    NSString *insertHealth = [NSString stringWithFormat:@"insert into Know(title,imgUrl,id) values('%@','%@','%ld')",title,imgUrl,id];
    int result = sqlite3_exec(dbPoint, [insertHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//查询
- (NSMutableArray *)selectKnowTable
{
    NSString *selectHealth = [NSString stringWithFormat:@"select * from Know"];
    //需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数,查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectHealth UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql跟随指针关联在一起,一同完成查询的操作
    //初始化装歌曲的大数组
    NSMutableArray *HealthArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *imgUrl = sqlite3_column_text(stmt, 1);
             int id = sqlite3_column_int(stmt, 2);
            //把取出来的内容进行类型转换
            NSString *titlehealth = [NSString stringWithUTF8String:(const char *)title];
            NSString *imgUrlhealth = [NSString stringWithUTF8String:(const char *)imgUrl];
            NSInteger idhealth = id;
            //给对象进行赋值
            Base *base = [[Base alloc] init];
            base.title = titlehealth;
            base.img =  imgUrlhealth;
            base.id = idhealth;
            [HealthArr addObject:base];
            [base release];
        }
    }else{
        NSLog(@"查询失败");
    }
    return HealthArr;
}
//删除内容
- (void)deleteKnowTableWithTitle:(NSString *)title
{
    NSString *deleteHealth = [NSString stringWithFormat:@"delete from Know where title = '%@'",title];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除表
- (void)dropKnowTable
{
    NSString *Health = [NSString stringWithFormat:@"drop table Know"];
    int result = sqlite3_exec(dbPoint, [Health UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}

//健康食谱表
//创建表
- (void)creatFoudTable
{
    //sql语句
    NSString *sqlStr = @"create table if not exists Foud(title text , imgUrl text, id int,number integer primary key autoincrement)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
//插入收藏
-(void)insertFoudthWithTitle:(NSString *)title imgUrl:(NSString *)imgUrl id:(NSInteger)id
{
    NSString *insertHealth = [NSString stringWithFormat:@"insert into Foud(title,imgUrl,id) values('%@','%@','%ld')",title,imgUrl,id];
    int result = sqlite3_exec(dbPoint, [insertHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//查询
- (NSMutableArray *)selectFoudTable
{
    NSString *selectHealth = [NSString stringWithFormat:@"select * from Foud"];
    //需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数,查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectHealth UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql跟随指针关联在一起,一同完成查询的操作
    //初始化装歌曲的大数组
    NSMutableArray *HealthArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *imgUrl = sqlite3_column_text(stmt, 1);
            int id = sqlite3_column_int(stmt, 2);
            //把取出来的内容进行类型转换
            NSString *titlehealth = [NSString stringWithUTF8String:(const char *)title];
            NSString *imgUrlhealth = [NSString stringWithUTF8String:(const char *)imgUrl];
            NSInteger idhealth = id;
            //给对象进行赋值
            Base *base = [[Base alloc] init];
            base.title = titlehealth;
            base.img =  imgUrlhealth;
            base.id = idhealth;
            [HealthArr addObject:base];
            [base release];
        }
    }else{
        NSLog(@"查询失败");
    }
    return HealthArr;
}
//删除内容
- (void)deleteFoudTableWithTitle:(NSString *)title
{
    NSString *deleteHealth = [NSString stringWithFormat:@"delete from Foud where title = '%@'",title];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除表
- (void)dropFoudTable
{
    NSString *Health = [NSString stringWithFormat:@"drop table Foud"];
    int result = sqlite3_exec(dbPoint, [Health UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}


//创建手术项目表
//创建表
- (void)creatOperationTable
{
    //sql语句
    NSString *sqlStr = @"create table if not exists Operation(title text , imgUrl text, id int,number integer primary key autoincrement)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
//插入收藏
-(void)insertOperationthWithTitle:(NSString *)title imgUrl:(NSString *)imgUrl id:(NSInteger)id
{
    NSString *insertHealth = [NSString stringWithFormat:@"insert into Operation(title,imgUrl,id) values('%@','%@','%ld')",title,imgUrl,id];
    int result = sqlite3_exec(dbPoint, [insertHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//查询
- (NSMutableArray *)selectOperationTable
{
    NSString *selectHealth = [NSString stringWithFormat:@"select * from Operation"];
    //需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数,查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectHealth UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql跟随指针关联在一起,一同完成查询的操作
    //初始化装歌曲的大数组
    NSMutableArray *HealthArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *imgUrl = sqlite3_column_text(stmt, 1);
            int id = sqlite3_column_int(stmt, 2);
            //把取出来的内容进行类型转换
            NSString *titlehealth = [NSString stringWithUTF8String:(const char *)title];
            NSString *imgUrlhealth = [NSString stringWithUTF8String:(const char *)imgUrl];
            NSInteger idhealth = id;
            //给对象进行赋值
            Base *base = [[Base alloc] init];
            base.title = titlehealth;
            base.img =  imgUrlhealth;
            base.id = idhealth;
            [HealthArr addObject:base];
            [base release];
        }
    }else{
        NSLog(@"查询失败");
    }
    return HealthArr;
}
//删除内容
- (void)deleteOperationTableWithTitle:(NSString *)title
{
    NSString *deleteHealth = [NSString stringWithFormat:@"delete from Operation where title = '%@'",title];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除表
- (void)dropOperationTable
{
    NSString *Health = [NSString stringWithFormat:@"drop table Operation"];
    int result = sqlite3_exec(dbPoint, [Health UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}


//创建检查项目
//创建表
- (void)creatInspectTable
{
    //sql语句
    NSString *sqlStr = @"create table if not exists Inspect(title text , imgUrl text, id int,number integer primary key autoincrement)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
//插入收藏
-(void)insertInspectthWithTitle:(NSString *)title imgUrl:(NSString *)imgUrl id:(NSInteger)id
{
    NSString *insertHealth = [NSString stringWithFormat:@"insert into Inspect(title,imgUrl,id) values('%@','%@','%ld')",title,imgUrl,id];
    int result = sqlite3_exec(dbPoint, [insertHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//查询
- (NSMutableArray *)selectInspectTable
{
    NSString *selectHealth = [NSString stringWithFormat:@"select * from Inspect"];
    //需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数,查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectHealth UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql跟随指针关联在一起,一同完成查询的操作
    //初始化装歌曲的大数组
    NSMutableArray *HealthArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *imgUrl = sqlite3_column_text(stmt, 1);
            int id = sqlite3_column_int(stmt, 2);
            //把取出来的内容进行类型转换
            NSString *titlehealth = [NSString stringWithUTF8String:(const char *)title];
            NSString *imgUrlhealth = [NSString stringWithUTF8String:(const char *)imgUrl];
            NSInteger idhealth = id;
            //给对象进行赋值
            Base *base = [[Base alloc] init];
            base.title = titlehealth;
            base.img =  imgUrlhealth;
            base.id = idhealth;
            [HealthArr addObject:base];
            [base release];
        }
    }else{
        NSLog(@"查询失败");
    }
    return HealthArr;
}
//删除内容
- (void)deleteInspectTableWithTitle:(NSString *)title
{
    NSString *deleteHealth = [NSString stringWithFormat:@"delete from Inspect where title = '%@'",title];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteHealth UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除表
- (void)dropInspectTable
{
    NSString *Health = [NSString stringWithFormat:@"drop table Inspect"];
    int result = sqlite3_exec(dbPoint, [Health UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}

//关闭数据库
- (void)closeDB
{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }else{
        NSLog(@"关闭数据失败");
    }
}
@end
